﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.IO;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Net;
using System.Text;


    public class clsXuLy
    {
        SqlCommand cmd;
        SqlDataAdapter da;
        DataTable dt;
        public string IP = System.Configuration.ConfigurationSettings.AppSettings["IP"].ToString();
        public string DB = System.Configuration.ConfigurationSettings.AppSettings["DB"].ToString();
        public SqlConnection conn;
        public clsXuLy()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public SqlConnection Connect()
        {
            try
            {
                //dùng config từ server
                string con = "";
               
                ////dùng chuỗi kết nối từ sql
                con = @"Data Source=" + IP + ";Initial Catalog=" + DB + ";User ID=" + "sa" + ";Password=" + "123456" + ";";
                conn = new SqlConnection(con);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                return conn;
            }
            catch
            {
                throw new Exception("Không kết nối được với cơ sở dữ liệu !");
            }

        }
        public string ConvertDecimal(double s)
        {
            string q = "";
            if (s.ToString().Contains("."))
            {
                double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
                q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
                q += s.ToString().Substring(s.ToString().IndexOf('.'));
            }
            else
                q = (s != 0) ? string.Format("{0:N0}", s) : s.ToString();
            return q;

            
        }
        public static string GetHost()
        {
            string Host = System.Configuration.ConfigurationSettings.AppSettings["Host"].ToString();
            return Host;

        }
        public string ConvertText(string text)
        {

            text = text.Replace(" ", "-");
            text = text.Replace(",", "-");
            text = text.Replace(";", "-");
            text = text.Replace(":", "-");

            Regex regex = new Regex(@"\p{IsCombiningDiacriticalMarks}+");

            string strFormD = text.Normalize(System.Text.NormalizationForm.FormD);
            return regex.Replace(strFormD, String.Empty).Replace('\u0111', 'd').Replace('\u0110', 'D');
        }
        public static string ConvertDau(string text)
        {

            text = text.Replace(" ", "-");
            text = text.Replace("/", "-");
            text = text.Replace("&", "-");
            text = text.Replace("*", "-");
            text = text.Replace("#", "-");
            text = text.Replace("?", "-");
            text = text.Replace("|", "-");
            text = text.Replace(",", "-");
            text = text.Replace(";", "-");
            text = text.Replace(":", "-");
            text = text.Replace("\"", "-");

            Regex regex = new Regex(@"\p{IsCombiningDiacriticalMarks}+");

            string strFormD = text.Normalize(System.Text.NormalizationForm.FormD);
            return regex.Replace(strFormD, String.Empty).Replace('\u0111', 'd').Replace('\u0110', 'D');
        }
        
        public void Close()
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            if (conn.State == ConnectionState.Broken)
                conn.Close();
            if (conn.State == ConnectionState.Connecting)
                conn.Close();
            if (conn.State == ConnectionState.Executing)
                conn.Close();
            if (conn.State == ConnectionState.Fetching)
                conn.Close();
            conn.Dispose();
        }
        public void ExecuteNonQuerry(string proc, SqlParameter[] param)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            cmd.Connection = Connect();
            cmd.ExecuteNonQuery();
            
        }
        public string ConvertToUnSign(string text)
        {
            for (int i = 32; i < 48; i++)
            {
                text = text.Replace(((char)i).ToString(), " ");
            }
            text = text.Replace(".", "-");
            text = text.Replace(" ", "-");
            text = text.Replace(",", "-");
            text = text.Replace(";", "-");
            text = text.Replace(":", "-");

            Regex regex = new Regex(@"\p{IsCombiningDiacriticalMarks}+");

            string strFormD = text.Normalize(System.Text.NormalizationForm.FormD);
            return regex.Replace(strFormD, String.Empty).Replace('\u0111', 'd').Replace('\u0110', 'D');
        }
        public static string ToUnSign(string text)
        {
            for (int i = 32; i < 48; i++)
            {
                text = text.Replace(((char)i).ToString(), " ");
            }
            text = text.Replace(".", "-");
            text = text.Replace(" ", "-");
            text = text.Replace(",", "-");
            text = text.Replace(";", "-");
            text = text.Replace(":", "-");

            Regex regex = new Regex(@"\p{IsCombiningDiacriticalMarks}+");

            string strFormD = text.Normalize(System.Text.NormalizationForm.FormD);
            return regex.Replace(strFormD, String.Empty).Replace('\u0111', 'd').Replace('\u0110', 'D');
        }
        public void ExecuteNonQuerry(string proc)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connect();
            cmd.ExecuteNonQuery();
            Close();
        }
        public void ExecuteNonQuerry_Text(string proc)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Connect();
            cmd.ExecuteNonQuery();
            Close();
        }
        public object GetNgayThang(string ns)
        {
            try
            {
                if (ns.Trim().Equals("")) return DBNull.Value;
                else
                {
                    string[] tmp = ns.Split('/');
                    string kq = tmp[1] + "/" + tmp[0] + "/" + tmp[2];
                    return kq;
                }
            }
            catch
            {
                return DBNull.Value;
            }
        }
        public object GetGuid(string ns)
        {
            try
            {
                if (ns.Trim().Equals("")) return DBNull.Value;
                else
                {
                    Guid ID = new Guid(ns);
                    return ID;
                }
            }
            catch
            {
                return DBNull.Value;
            }
        }
        public bool SendMail(string emailadmin, string mkemail, string tieude, string Mail, string content)
        {
            try
            {
                int port = 587;
                SmtpClient client = new SmtpClient();
                client.EnableSsl = true;
                client.Port = port;
                client.Host = "smtp.gmail.com";
                client.Credentials = new NetworkCredential(emailadmin, mkemail);
                MailAddress from = new MailAddress(emailadmin, "THÔNG TIN ĐƠN ĐẶT HÀNG");
                MailAddress toAddress = new MailAddress(Mail);
                MailMessage message = new MailMessage(from, toAddress);
                message.IsBodyHtml = true;
                message.Body = content;
                message.Subject = tieude;
                client.Send(message);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public DataTable GetData(string proc)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            return dt;
        }
        public DataTable GetData_Text(string sql)
        {
            cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            return dt;
        }

        public DataTable GetData(string proc, SqlParameter[] param)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            return dt;
        }
        public void Insert_NhatKi(Guid ID, string chucnang, string thaotac, string dulieucu, string dulieumoi, string taikhoan)
        {
            SqlParameter[] p;
            p = new SqlParameter[7];
            p[0] = new SqlParameter("@ID", ID);
            p[0].SqlDbType = SqlDbType.UniqueIdentifier;
            p[1] = new SqlParameter("@Chucnang", chucnang);
            p[1].SqlDbType = SqlDbType.NVarChar;
            p[2] = new SqlParameter("@Thaotac", thaotac);
            p[2].SqlDbType = SqlDbType.NVarChar;
            p[3] = new SqlParameter("@DuLieuCu", dulieucu);
            p[3].SqlDbType = SqlDbType.NVarChar;
            p[4] = new SqlParameter("@DuLieuMoi", dulieumoi);
            p[4].SqlDbType = SqlDbType.NVarChar;
            p[5] = new SqlParameter("@Thoigian", DateTime.Now);
            p[5].SqlDbType = SqlDbType.NVarChar;
            p[6] = new SqlParameter("@Taikhoan", taikhoan);
            p[6].SqlDbType = SqlDbType.NVarChar;
            ExecuteNonQuerry("sp_NhatKi_Insert", p);
            Close();
        }

        public int GetValueInt(string proc)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            if (dt.Rows.Count == 0)
                return 0;
            else return int.Parse(dt.Rows[0][0].ToString());
        }

        public int GetValueInt_Text(string sql)
        {
            cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            if (dt.Rows.Count == 0)
                return 0;
            else return int.Parse(dt.Rows[0][0].ToString());
        }

        public int GetValueInt(string proc, SqlParameter[] param)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            string i = "";
            if (dt.Rows.Count == 0)
                return 0;
            else
            {
                i = dt.Rows[0][0].ToString();
            }
            return int.Parse(i);
        }
        public void WriteLog(string filepath, string content)
        {
            FileStream fs = new FileStream(filepath, FileMode.Append);
            StreamWriter sWriter = new StreamWriter(fs, Encoding.Unicode);
            sWriter.WriteLine(content);
            sWriter.Close();
        }
        public string GetValueString(string proc)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            if (dt.Rows.Count == 0)
                return "";
            else return dt.Rows[0][0].ToString();
        }


        public string GetValueString(string proc, SqlParameter[] param)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            if (dt.Rows.Count == 0)
                return "";
            else return dt.Rows[0][0].ToString();
        }
        public string GetValueString_Text(string sql)
        {
            cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            Close();
            if (dt.Rows.Count == 0)
                return "0";
            else return dt.Rows[0][0].ToString();

        }

        public float GetValueFloat(string proc, SqlParameter[] param)
        {
            cmd = new SqlCommand();
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            cmd.Connection = Connect();
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            float i = 0;
            if (dt.Rows.Count == 0)
                i = 0;
            else i = Convert.ToSingle(cmd.ExecuteScalar().ToString());
            Close();
            return i;
        }
        public string Encrypt(string clearText)
        {
            try
            {
                byte[] clearBytes = System.Text.Encoding.Unicode.GetBytes(clearText);
                PasswordDeriveBytes pdb = new PasswordDeriveBytes("gsmarttech",
                   new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                byte[] encryptedData = Encrypt(clearBytes, pdb.GetBytes(32), pdb.GetBytes(16));
                return Convert.ToBase64String(encryptedData);
            }
            catch (Exception e)
            {
                string ex = e.ToString();
                return "";
            }
        }
        private byte[] Encrypt(byte[] clearData, string Key)
        {
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Key,
                          new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            return Encrypt(clearData, pdb.GetBytes(32), pdb.GetBytes(16));
        }
        private byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
        {
            MemoryStream ms = new MemoryStream();
            Rijndael alg = Rijndael.Create();
            alg.Key = Key;
            alg.IV = IV;
            CryptoStream cs = new CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(clearData, 0, clearData.Length);
            cs.Close();
            byte[] encryptedData = ms.ToArray();
            return encryptedData;
        }
        private void Encrypt(string fileIn, string fileOut, string Key)
        {
            FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read);
            FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write);
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Key,
                        new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            Rijndael alg = Rijndael.Create();
            alg.Key = pdb.GetBytes(32);
            alg.IV = pdb.GetBytes(16);
            CryptoStream cs = new CryptoStream(fsOut, alg.CreateEncryptor(), CryptoStreamMode.Write);
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];
            int bytesRead;

            do
            {

                bytesRead = fsIn.Read(buffer, 0, bufferLen);
                cs.Write(buffer, 0, bytesRead);
            } while (bytesRead != 0);

            cs.Close();
            fsIn.Close();

        }
        public string StreamToString(Stream stream)
        {
            byte[] bytes = new byte[stream.Length];

            stream.Position = 0;

            stream.Read(bytes, 0, (int)stream.Length);
            string base64String = Convert.ToBase64String(bytes);
            return base64String;

        }
        public Stream StringToStream(string str)
        {
            byte[] imageBytes = Convert.FromBase64String(str);
            MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
            ms.Write(imageBytes, 0, imageBytes.Length);

            return ms;
        }

        public DataTable GetHangMuc()
        {
            return GetData("sp_TLThanhToanTienMat_HangMucChi");
        }
        public DataTable GetDonVi()
        {
            return GetData("sp_TLThanhToanTienMat_DonVi");
        }
        public DataTable GetDoiTuong()
        {
            return GetData("sp_TLThanhToanTienMat_DoiTuongChi");
        }

        public DataTable GetNCC() {
            return GetData("xdaTLThanhToanTienMat");
        }
        public DataTable GetNhomVatLieu()
        {
            return GetData("xdaTLThanhToanTienMat_NhomVatLieuList");
        }
        public DataTable GetLoaiVatLieu()
        {
            return GetData("xdaTLThanhToanTienMat_LoaiVatLieuList");
        }
        public DataTable GetLoaiVL()
        {
            return GetData("sp_xdaloaivatlieu_LoadDrop");
        }
        public DataTable GetMaThietBi()
        {
            return GetData("sp_xdanhapkho_MaThietBi_List");
        }
    }
